<?php
class SQLiteDB
{
    protected static $_sqlite = null;

    protected static $_cache = [];

    public static function sqLite()
    {
        if (is_null(self::$_sqlite)) {
            self::$_sqlite = new \PDO(sprintf('sqlite:%s', ROOT_DIR . '/lib/database.sqlite'));
            self::$_sqlite->exec('set names utf8'); 
        }
        return self::$_sqlite;
    }

    public static function getUser()
    {
        $sth = self::sqLite()->prepare('SELECT `name` FROM `user` ORDER BY `name` ASC');
        $sth->execute();
        return $sth->fetchAll(\PDO::FETCH_COLUMN, 0);
    }

    public static function addUser($user)
    {
        try {
            $res = self::insert('user', $user);
        } catch (\Exception $e) {
            $res = false;
        }

        return $res;
    }

    public static function getRows($conditions=[])
    {
        $sql = 'SELECT * FROM `main`';
        if (!empty($conditions)) {
            $where = [];
            foreach ($condigions as $condition) {
                $where[] = $condigion ;
            }
            if (!empty($where)) {
                $sql .= sprintf(' WHERE %s', implode(' ', $where));
            }
        }
        $sth = self::sqLite()->prepare($sql);
        $sth->execute();
        return $sth->fetchAll(\PDO::FETCH_ASSOC);
    }

    public static function updateRow($key, $user, $ver)
    {
        $sth = self::sqLite()->prepare("UPDATE `main` SET `user`=:user, `ver`=:ver WHERE `key`=:key");
        return $sth->execute([':user' => $user, ':ver' => $ver, ':key' => $key]);
    }

    public static function addRow($data)
    {
        try {
            $res = self::insert('main', $data);
            if (!$res) {
                $res = self::updateRow($data['key'], $data['user'], $data['ver']??'');
            }
        } catch (\Exception $e) {
            // echo $e->getMessage();
            $res = false;
        }

        return $res;
    }

    public static function deleteRow($table, $condigions='')
    {
        $sql = 'DELETE FROM `%s`';
        if ($condigions) {
            $sql .= ' WHERE ' . $condigions;
        }
        self::sqLite()->query(sprintf($sql, $table));
    }

    public static function deleteUser($user)
    {
        return self::deleteRow('user', sprintf('name="%s"', $user));
    }

    protected static function insert($table, $data)
    {
        $fields = self::getCache('fields', $table);
        if (is_null($fields)) {
            $fields = self::getTableFields($table);
            self::setCache('fields', $table, $fields);
        }
        $insertField = array_intersect_key($data, $fields);
        $values = [];
        $_fields = [];
        foreach ($insertField as $field => $value)
        {
            $_fields[$field] = ':' . $field;
            $values[$_fields[$field]] = $value;
        }
        if (empty($_fields)) {
            // die('Can not do insert, please check insert field');
            return false;
        }

        $sql = "INSERT INTO %s(%s) VALUES(%s)";
        $sql = sprintf($sql, $table, implode(',', array_keys($_fields)), implode(',', $_fields));
        $sth = self::sqLite()->prepare($sql);
        $sth->execute($values);
        return self::sqLite()->lastInsertId();
    }

    protected static function getTableFields($table)
    {
        $sth = self::sqLite()->prepare(sprintf("PRAGMA TABLE_INFO('%s')", $table));
        $sth->execute();
        $struct = $sth->fetchAll(\PDO::FETCH_ASSOC);

        $fields = [];
        foreach ($struct as $field) {
            if ($field=='id') continue;
            $fields[$field['name']] = $field['name'];
        }
        return $fields;
    }

    protected static function setCache($nameSpace, $key, $data)
    {
        if (!isset(self::$_cache[$nameSpace])) {
            self::$_cache[$nameSpace] = [];
        }
        self::$_cache[$nameSpace][$key] = $data;
    }

    protected static function getCache($nameSpace, $key)
    {
        if (isset(self::$_cache[$nameSpace])) {
            if (isset(self::$_cache[$nameSpace][$key])) {
                return self::$_cache[$nameSpace][$key];
            }
        }
        return null;
    }
}